Relationships Versus Joins and Data Blending in Tableau

Course: Tableau Training

Topic: Relationships Versus Joins and Blending

Objective: Understand how Tableau combines data using relationships, joins, and data blending; learn their differences, advantages, limitations, practical applications, and best practices for building accurate dashboards.

Course Links: Explore Tableau Training | Register for a Tableau Course Demo

1. Introduction to Combining Data in Tableau

In business intelligence, data is often stored in multiple tables, databases, Excel files, or published data sources. For example, a retail company may store orders, customer information, product details, and sales targets in separate tables. To analyze business performance effectively, these datasets need to be connected or combined appropriately.

Tableau provides three important approaches for combining data: relationships, joins, and data blending. Although all three methods help analyze information from multiple tables or sources, they work differently and are suited to different situations.

• Relationships: Connect logical tables while allowing each table to retain its structure and level of detail.
• Joins: Combine physical tables into a single table using matching fields and a specified join type.
• Data blending: Combines aggregated results from separate data sources in a worksheet visualization.

Choosing the right approach is important because the wrong method can produce duplicated measures, missing records, unexpected null values, or incorrect business conclusions.

2. What Are Relationships in Tableau?

A relationship is a connection between two logical tables based on corresponding fields. Relationships allow Tableau to understand how tables are associated without physically merging them into one flat table.

Relationships are defined in the logical layer of Tableau's data model. Tableau uses the fields in a visualization to determine how to query the related tables and combine their results.

Example: Orders and Customers

Suppose an online store maintains two tables.

Orders Table

Customers Table

The tables can be related using Customer ID.

Orders.Customer ID
        |
        | Relationship
        |
Customers.Customer ID

After defining the relationship, a worksheet can show total sales by customer name. Tableau uses the relationship to retrieve the relevant customer and order information.

Key Characteristics of Relationships

• Relationships connect logical tables in the logical layer.
• The tables remain separate in the data model.
• Users define matching fields rather than selecting a fixed join type.
• Tableau determines the necessary query behavior based on the visualization.
• Relationships support analysis across tables with different levels of detail.
• They can reduce duplication and record-loss problems that may occur with inappropriate joins.
• Relationships are generally the recommended starting point for combining tables in Tableau.

Advantages of Relationships

• Preserve the native level of detail of individual logical tables.
• Support flexible analysis across multiple tables.
• Reduce the need to choose an inner, left, right, or full join in advance.
• Can help prevent duplicated aggregate values caused by one-to-many joins.
• Allow Tableau to query only the tables relevant to the current view when possible.
• Make it easier to maintain reusable data models for multiple worksheets.

Limitations of Relationships

• Matching fields must have compatible underlying data types.
• Incorrect relationship keys can produce inaccurate results.
• Relationships do not automatically clean dirty or duplicated source data.
• Complex or poorly structured data may require additional preparation.
• Some data-modeling scenarios have connector- or version-specific limitations.

3. What Are Joins in Tableau?

A join combines two physical tables into a single table based on one or more matching conditions. Unlike a relationship, a join establishes a fixed table combination before the worksheet analysis begins.

Joins are configured in the physical layer of Tableau's data model. Users choose the fields used to match records and select the appropriate join type.

Example: Orders and Customers

Suppose the Orders table contains Order ID, Customer ID, and Sales, while the Customers table contains Customer ID, Customer Name, and City.

A join on Customer ID can produce a table containing both order information and customer details.

The combined table contains fields from both physical tables. The actual rows returned depend on the join type and the values present in each source.

Types of Joins in Tableau

3.1 Inner Join

An inner join returns only records that have matching values in both tables.

Table A INNER JOIN Table B

Example: Display orders only when a matching customer record exists.

3.2 Left Join

A left join returns all records from the left table and matching records from the right table. If no match exists, the right-side fields contain null values.

Table A LEFT JOIN Table B

Example: Retain every order, including orders that do not have a matching customer record.

3.3 Right Join

A right join returns all records from the right table and matching records from the left table. If no match exists, the left-side fields contain null values.

Table A RIGHT JOIN Table B

Example: Retain every customer, including customers who have not placed an order, when Customers is the right-hand table.

3.4 Full Outer Join

A full outer join returns matching records and unmatched records from both tables.

Table A FULL OUTER JOIN Table B

Example: Compare two customer databases and retain customers who appear in only one database as well as customers who appear in both.

Advantages of Joins

• Provide explicit control over how tables are combined.
• Allow users to choose the required join type.
• Make fields from joined tables available in one combined physical table.
• Are useful when a single flattened table is required.
• Can support specific data-preparation requirements where intentional filtering or duplication is needed.

Limitations of Joins

• Incorrect join conditions can create inaccurate results.
• One-to-many or many-to-many matches can multiply rows.
• Measures can be duplicated when tables have different levels of detail.
• Inner joins can exclude unmatched records.
• Outer joins can introduce null values for unmatched records.
• Join types and conditions remain fixed until the data model is changed.

4. What Is Data Blending in Tableau?

Data blending is a method for combining data from separate data sources in a visualization. Tableau queries the sources separately, aggregates their results at the relevant level, and presents the results together in the worksheet.

Unlike joins and relationships, blending does not create a single combined table or a reusable blended data source. The blend is established in the context of an individual worksheet.

Example: Actual Sales and Sales Targets

A company stores actual sales in one data source and sales targets in another. The actual sales data may come from a transactional database, while the targets are stored in a separately published Tableau data source.

Blending can help compare the aggregated sales and target values in one visualization when the sources cannot or should not be combined directly through a relationship or join.

Primary Data Source: Actual Sales
             |
             | Linking Field
             |
Secondary Data Source: Sales Targets
             |
             v
      Combined Worksheet View

Primary and Secondary Data Sources

Primary data source: The data source that establishes the main context of the worksheet. The first data source used to build the view typically becomes the primary source.

Secondary data source: An additional source that contributes fields or aggregated values to the worksheet through data blending.

How Data Blending Works

1. Connect Tableau to the first data source.
2. Connect Tableau to the second data source.
3. Start a worksheet using fields from the intended primary data source.
4. Switch to the secondary data source and identify a suitable linking field.
5. Use fields from the secondary source in the same worksheet.
6. Confirm that the linking field is active and that the values match correctly.
7. Review the aggregated results and investigate missing values.

Key Characteristics of Data Blending

• Combines information from separate data sources in a worksheet.
• Uses primary and secondary data sources.
• Aggregates data before combining the results in the view.
• Uses linking fields to align the aggregated results.
• Can be useful when working with published data sources or certain incompatible source types.
• Can be configured differently for individual worksheets.
• Does not create a new blended data source that can be published as one combined source.
• Behaves like a left-side combination from the perspective of the primary source, so unmatched secondary-source values may not appear in the result.

Advantages of Data Blending

• Can combine information from separate data sources without first physically merging their underlying tables.
• Useful when comparing aggregated results from independently maintained sources.
• Supports worksheet-specific linking behavior.
• Can help when a direct join or relationship is not suitable for the available sources.

Limitations of Data Blending

• Results depend on the primary data source and linking fields.
• Unmatched values from the secondary source may not be represented in the view.
• Differences in aggregation and granularity can complicate calculations.
• It does not create one reusable, publishable blended data source.
• It may have limitations compared with relationships for complex multi-table analysis.

5. Relationships vs Joins vs Data Blending

6. Understanding Level of Detail

The level of detail describes what one row represents in a dataset. It may represent one order, one customer, one product, one transaction, or one order item.

Understanding the level of detail is essential when choosing between relationships, joins, and blending.

Example: Orders and Order Items

The Orders table has one row per order. The Order Items table has one row per product line in an order.

Orders

Order Items

If these tables are joined on Order ID, order 501 appears twice because it contains two items.

The original total of Order Total is 1500, but summing the joined Order Total values produces 2500. The order-level measure is repeated for each matching item row.

How to Avoid This Problem

• Use relationships when the tables have different levels of detail and should remain logically separate.
• Inspect the uniqueness of join keys before joining tables.
• Compare totals before and after combining data.
• Use appropriate aggregations and calculations for the business question.
• Consider pre-aggregating data in Tableau Prep or the source database when necessary.

7. Step-by-Step: Creating a Relationship

1. Open Tableau and connect to the data source.
2. Navigate to the Data Source page.
3. Drag the first table, such as Orders, onto the logical-layer canvas.
4. Drag the second table, such as Customers, onto the canvas.
5. Allow Tableau to create a relationship if it detects suitable matching fields.
6. Click the relationship line to inspect its configuration.
7. Select the appropriate field pair, such as Customer ID in both tables.
8. Confirm that the underlying field data types are compatible.
9. Check cardinality and referential-integrity settings if applicable.
10. Open a worksheet and validate the results with fields from both tables.

When to Use a Relationship

Use a relationship when tables contain related information but should retain their own structure, particularly when the tables have different levels of detail or when the same data model needs to support multiple types of analysis.

8. Step-by-Step: Creating a Join

1. Connect Tableau to the relevant data source.
2. Open the Data Source page.
3. Drag the first table onto the logical-layer canvas.
4. Double-click the logical table to open the physical layer.
5. Drag the second physical table into the physical-layer canvas.
6. Click the join icon between the tables.
7. Select the required join type.
8. Specify the matching fields and join conditions.
9. Inspect the combined data grid.
10. Check for missing records, null values, and duplicated measures.
11. Return to the logical layer and test the data in a worksheet.

When to Use a Join

Use a join when you need explicit control over how records are combined or when a single physical table is required for a specific data-preparation or analytical purpose.

9. Step-by-Step: Creating a Data Blend

1. Connect Tableau to the first data source.
2. Connect to the second data source.
3. Open a new worksheet.
4. Choose the primary data source and drag a dimension such as Region into the view.
5. Switch to the secondary data source.
6. Identify the field that links the two sources, such as Region.
7. Activate or configure the appropriate linking field in the Data pane.
8. Drag a measure from the secondary source into the worksheet.
9. Inspect the aggregated values and check for unmatched records.
10. Confirm that the primary and secondary measures use compatible levels of detail.

Example

Use Region from the primary Sales source, SUM(Sales) from the primary source, and SUM(Sales Target) from the secondary source. Compare the values in a chart, ensuring that the regional names match across both sources.

10. Practical Business Scenario: Sales and Targets

Scenario: A retail company wants to compare actual sales with monthly targets. Actual sales are stored in a transactional database, while targets are stored in a separate published data source.

Option A: Relationships

Use relationships when the available tables can be modeled together appropriately and their data sources support the required relationship. Relationships are generally the preferred starting point for flexible multi-table analysis.

Option B: Joins

Use a join when the tables can be combined through a suitable join condition and a fixed physical table is required. Confirm that the target data's grain matches the sales data's grain or aggregate the data appropriately to avoid repeated target values.

Option C: Data Blending

Use blending when the sources must remain separate and their aggregated results need to be compared within a worksheet, such as when working with certain published data sources.

Recommended Validation

• Confirm that both sources use the same region names or standardized region codes.
• Check that target periods match the sales periods.
• Confirm whether the target is recorded per region, per month, or per product.
• Check whether any source contains duplicate region-period combinations.
• Verify that actual sales and target values are aggregated at the same intended level.
• Investigate missing values before interpreting performance.

11. Calculating Sales Achievement

After the data has been modeled correctly, a common business calculation is the percentage of target achieved.

SUM([Sales]) / SUM([Sales Target])

Format the calculation as a percentage. This formula is valid when Sales and Sales Target are correctly aligned at the same analytical level and each target is counted only as intended.

If the target value is repeated across individual transaction rows, a direct join can inflate the target total. Review the model and aggregation strategy before using the result for business decisions.

12. Choosing the Correct Method

13. Common Mistakes and Solutions

Mistake 1: Assuming Relationships and Joins Are the Same

Problem: A user assumes that relationships immediately merge tables into one fixed table.

Solution: Remember that relationships connect logical tables, while joins combine physical tables.

Mistake 2: Using Joins Without Checking Data Grain

Problem: A one-to-many join repeats measures and can inflate totals.

Solution: Check key uniqueness, inspect row counts, and validate aggregate results.

Mistake 3: Selecting the Wrong Primary Data Source

Problem: In data blending, the primary source determines the main context of the view. Records available only in the secondary source may not appear as expected.

Solution: Choose the primary source according to the analysis objective and inspect unmatched values.

Mistake 4: Using Incorrect Linking Fields

Problem: Inconsistent region names or customer identifiers prevent correct matches.

Solution: Standardize field values and validate the linking fields.

Mistake 5: Assuming Relationships Fix All Data Problems

Problem: Duplicate records and dirty source data can still cause incorrect results.

Solution: Clean the source data and validate the data model before analysis.

Mistake 6: Comparing Measures at Different Levels of Detail

Problem: Daily sales are compared directly with annual targets without appropriate aggregation.

Solution: Align the measures at the intended reporting level and confirm that each value is counted correctly.

14. Best Practices

• Use relationships as the first option for most multi-table analytical data models.
• Use joins when a fixed physical combination is necessary or when you specifically need the behavior of a selected join type.
• Use blending when separate data sources need to be combined through aggregated results in a worksheet and a relationship or join is unsuitable.
• Check the level of detail of every source table.
• Standardize keys, field names, and data types.
• Validate row counts, totals, and unmatched records.
• Review null values rather than automatically replacing them.
• Use accurate relationship cardinality and referential-integrity settings when configuring them.
• Test the model with multiple worksheets and business questions.
• Use Tableau Prep or another data-preparation tool for complex cleaning and transformation tasks.
• Document the reasons for choosing a relationship, join, or blend.
• Verify all important calculations before publishing the dashboard.

15. Hands-On Practice Exercise

Scenario: Create a Tableau report comparing sales by customer and actual sales against regional targets.

Orders Table

Customers Table

Sales Targets Table

Tasks

1. Create a relationship between Orders and Customers using Customer ID.
2. Create a worksheet showing sales by customer.
3. Connect the Sales Targets table and evaluate whether it is appropriate to use a relationship or blend for the reporting requirement.
4. Compare sales and targets by region.
5. Calculate percentage of target achieved.
6. Validate that each target is counted only once at the intended region level.
7. Compare the outcome with what would happen if the targets were joined directly to individual order rows.
8. Create a dashboard containing the customer and regional performance views.

Expected Sales by Customer

Expected Sales by Region

Note: The target data is at the region level, while Orders is at the transaction level. A direct join can repeat regional target values for every order in that region. Validate the target aggregation and use an appropriate relationship, blend, or pre-aggregated model to ensure that the target is not counted multiple times.

16. Interview Questions and Answers

Q1. What is a relationship in Tableau?

Answer: A relationship connects logical tables using matching fields while allowing the tables to retain their separate structures and levels of detail.

Q2. What is a join in Tableau?

Answer: A join combines physical tables into one table using matching conditions and a selected join type.

Q3. What is data blending?

Answer: Data blending combines aggregated results from separate data sources in a worksheet visualization using primary and secondary sources and linking fields.

Q4. What is the main difference between relationships and joins?

Answer: Relationships preserve separate logical tables and defer query decisions to the analysis. Joins combine physical tables into one table using a fixed join type and condition.

Q5. What is the main difference between joins and blending?

Answer: Joins combine row-level table data before analysis. Blending aggregates the source results separately and combines them visually in a worksheet.

Q6. When should you use relationships?

Answer: Use relationships for flexible analysis across related tables, especially when the tables have different levels of detail.

Q7. When should you use joins?

Answer: Use joins when a fixed combined table is required or when explicit control over join behavior is necessary.

Q8. When should you use data blending?

Answer: Use blending when separate data sources need to be combined through aggregated results in a worksheet, particularly when relationships or direct joins are unsuitable.

Q9. Can joins duplicate measures?

Answer: Yes. If one record matches multiple records in another table, values from the first table may repeat in the joined result, potentially inflating sums.

Q10. Does a relationship physically merge tables?

Answer: No. A relationship connects logical tables while preserving their separate structures.

Q11. What are primary and secondary data sources?

Answer: The primary source establishes the main context of a blended worksheet. The secondary source contributes additional aggregated values through linking fields.

Q12. Does blending create a new reusable data source?

Answer: No. The blended result is defined in the worksheet and is not a new combined data source that can be published as one blended source.

Q13. What happens when a record does not match in an inner join?

Answer: The unmatched record is excluded from the inner-join result.

Q14. What is the risk of joining regional targets to individual orders?

Answer: Each regional target may be repeated for every order in that region. Summing the joined target values can therefore overstate the target total.

Q15. Which method is recommended as the starting point for combining tables in Tableau?

Answer: Relationships are generally recommended as the first approach for multi-table analysis, while joins and blending remain useful for specific requirements.

17. Quick Revision Notes

• Relationship: Connects logical tables without physically merging them.
• Join: Combines physical tables into one table.
• Data blending: Combines aggregated results from separate sources in a worksheet.
• Primary source: Establishes the main context of a blended view.
• Secondary source: Contributes additional aggregated data to a blended view.
• Inner join: Returns matching records only.
• Left join: Preserves all left-table records.
• Right join: Preserves all right-table records.
• Full outer join: Preserves matched and unmatched records from both tables.
• Level of detail: Describes what one row represents in a table.
• Recommended starting point: Relationships for most flexible multi-table analytical models.
• Important validation: Check duplicates, null values, row counts, linking fields, and aggregate totals.

18. Conclusion

Relationships, joins, and data blending are essential Tableau skills for combining information from multiple tables and data sources. Relationships provide flexible connections between logical tables, joins create fixed combinations of physical tables, and blending combines aggregated results from separate sources within a worksheet.

The best method depends on the source structure, data compatibility, level of detail, and reporting objective. By understanding these differences and validating the resulting calculations, Tableau users can build accurate, reusable, and reliable dashboards.

Continue Learning: Explore Tableau Training | Register for a Tableau Course Demo

19. Official Tableau Learning Resources

• Tableau Official Documentation: Relationships
• Tableau Official Documentation: Joins
• Tableau Official Documentation: Data Blending
• Tableau Official Documentation: Relationships, Data Models, and Data Sources FAQ